package aes.server;

import java.sql.*;
import java.util.ArrayList;
import java.util.Arrays;
import common.*;

public class ReportManager {

	public static Integer[] getListOfStudentThatApproved(){
		ArrayList<Integer> arr=new ArrayList<Integer>();
		Integer i;
		DBManager db = DBManager.getInstance();	
		String sql="SELECT distinct studentId "+ 
				"FROM aes.student_exam "+
				"WHERE isApproved=1";
		try {
			PreparedStatement stmt1 = db.getConnection().prepareStatement(sql);
			ResultSet rs1 = stmt1.executeQuery();
			while(rs1.next()){
				i=rs1.getInt("studentId");
				arr.add(i);
			}
			rs1.close();
			Integer[] ret = new Integer[arr.size()];
			arr.toArray(ret);
			return ret;	
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}

	}

	public static Integer[] grtStudentGrades(int sid){
		ArrayList<Integer> arr=new ArrayList<Integer>();
		Integer i;
		DBManager db = DBManager.getInstance();	
		String sql="SELECT  grade " +
				"FROM aes.student_exam "+
				"WHERE studentId=? AND isApproved=1";
		try {
			PreparedStatement stmt1 = db.getConnection().prepareStatement(sql);
			stmt1.setInt(1,sid);
			ResultSet rs1 = stmt1.executeQuery();
			while(rs1.next()){
				i=rs1.getInt("grade");
				arr.add(i);
			}
			rs1.close();
			Integer[] ret = new Integer[arr.size()];
			arr.toArray(ret);
			return ret;	
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}


	}

	/**
	 * Get outoExam ID list
	 * @return outoExam IDs list
	 */
	public static Integer[] getAutoExamIdList(){
		ArrayList<Integer> arr=new ArrayList<Integer>();
		Integer i;
		DBManager db = DBManager.getInstance();	
		String sql="SELECT distinct autoExamId "+
				"FROM aes.exam_execution "+ 
				"WHERE status=4";
		try {
			PreparedStatement stmt1 = db.getConnection().prepareStatement(sql);
			ResultSet rs1 = stmt1.executeQuery();
			while(rs1.next()){
				i=rs1.getInt("autoExamId");
				arr.add(i);
			}
			rs1.close();
			Integer[] ret = new Integer[arr.size()];
			arr.toArray(ret);
			return ret;	
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}


	}

	public static String getCourseName(int cid){
		DBManager db = DBManager.getInstance();	
		String s;
		String sql="SELECT cName "+
				"FROM aes.courses "+
				"WHERE cId=?";
		try {
			PreparedStatement stmt1 = db.getConnection().prepareStatement(sql);
			stmt1.setInt(1, cid);
			ResultSet rs1 = stmt1.executeQuery();
			if (!rs1.next())
				return null;
			s=rs1.getString("cName");
			rs1.close();
			return s;	
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	public static boolean verifyExamInfo(int code){
		DBManager db = DBManager.getInstance();	
		String sql="select q1.c as napp, q2.c as app, ee.status " +
				"from (SELECT count(studentId)as c FROM student_exam se where se.examCode=? and isCheater=0 and se.isApproved=0) as q1, (SELECT count(studentId)as c FROM student_exam se where se.examCode=? and isCheater=0 and se.isApproved=1) as q2, exam_execution ee, student_exam se " + 
				"where ee.code=se.examCode and se.examCode=? " +
				"group by se.examcode";
		try {
			PreparedStatement stmt1 = db.getConnection().prepareStatement(sql);
			stmt1.setInt(1,code);
			stmt1.setInt(2,code);
			stmt1.setInt(3,code);
			ResultSet rs1 = stmt1.executeQuery();
			boolean bool=false;
			rs1.next();
			bool=(rs1.getInt("napp")==0 && rs1.getInt("app")>0 && rs1.getInt("status")==4);
			rs1.close();
			return bool;	
		} catch (SQLException e) {
			e.printStackTrace();
			return (Boolean) null;
		}
	}

	/**
	 * Retrieves statistic report by course
	 * @param courseId Course Id
	 * @return Report
	 */
	public static StatisticReportItem getCoursesStatisticReport(int courseId) {
		DBManager db = DBManager.getInstance();	
		String sql = "SELECT se.grade FROM aes.exam_execution ee, aes.student_exam se " +
					 "WHERE ee.code=se.examCode AND CAST((ee.autoExamId%10000)/100 AS SIGNED)=? AND ee.status=4 AND se.isApproved=1;";
		try {
			StatisticReportItem res = new StatisticReportItem();
			PreparedStatement stmt = db.getConnection().prepareStatement(sql);
			stmt.setInt(1, courseId);
			
			ResultSet rs = stmt.executeQuery();
			int sum = 0, count = 0;
			int[] grades = new int[] {0, 0, 0, 0, 0, 0, 0, 0, 0};
			ArrayList<Integer> lstgrades = new ArrayList<Integer>();
			while (rs.next()) {
				int grade = rs.getInt("grade");
				lstgrades.add(grade);
				if(grade >=0 && grade<55){
					grades[0]++;
				}
				else if(grade >=55 && grade<65){
					grades[1]++;
				}
				else if(grade >=65 && grade<70){
					grades[2]++;
				}
				else if(grade >=70 && grade<75){
					grades[3]++;
				}
				else if(grade >=75 && grade<80){
					grades[4]++;
				}
				else if(grade >=80 && grade<85){
					grades[5]++;
				}
				else if(grade >=85 && grade<90){
					grades[6]++;
				}
				else if(grade >=90 && grade<95){
					grades[7]++;
				}
				else if(grade >=95 && grade<=100){
					grades[8]++;
				}
				sum += grade;
				count++;
			}
			rs.close();
			if (count > 0) {
				for (int i=0; i<9; i++)
					grades[i] = (int)(grades[i]*100/count);
				Integer[] ret = new Integer[lstgrades.size()];
				lstgrades.toArray(ret);
				
				Arrays.sort(ret);
				int med = (ret.length / 2) - 1;
				if (med < 0)
					med = 0;
				
				res.setMedian(ret[med]);
				res.setDestribution(grades);
				res.setAverage(sum/count);
			}
			return res;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}
	
	/**
	 * Retrieves statistic report by teacher
	 * @param teacherId Teacher Id
	 * @return Report
	 */
	public static StatisticReportItem getTeacherStatisticReport(int teacherId) {
		DBManager db = DBManager.getInstance();	
		String sql = "SELECT se.grade FROM aes.exam_execution ee, aes.student_exam se " +
					 "WHERE ee.code=se.examCode AND ee.execTeacherId=? AND ee.status=4 AND se.isApproved=1;";
		try {
			StatisticReportItem res = new StatisticReportItem();
			PreparedStatement stmt = db.getConnection().prepareStatement(sql);
			stmt.setInt(1, teacherId);
			
			ResultSet rs = stmt.executeQuery();
			int sum = 0, count = 0;
			int[] grades = new int[] {0, 0, 0, 0, 0, 0, 0, 0, 0};
			ArrayList<Integer> lstgrades = new ArrayList<Integer>();
			while (rs.next()) {
				int grade = rs.getInt("grade");
				lstgrades.add(grade);
				if(grade >=0 && grade<55){
					grades[0]++;
				}
				else if(grade >=55 && grade<65){
					grades[1]++;
				}
				else if(grade >=65 && grade<70){
					grades[2]++;
				}
				else if(grade >=70 && grade<75){
					grades[3]++;
				}
				else if(grade >=75 && grade<80){
					grades[4]++;
				}
				else if(grade >=80 && grade<85){
					grades[5]++;
				}
				else if(grade >=85 && grade<90){
					grades[6]++;
				}
				else if(grade >=90 && grade<95){
					grades[7]++;
				}
				else if(grade >=95 && grade<=100){
					grades[8]++;
				}
				sum += grade;
				count++;
			}
			rs.close();
			if (count > 0) {
				for (int i=0; i<9; i++)
					grades[i] = (int)(grades[i]*100/count);
				Integer[] ret = new Integer[lstgrades.size()];
				lstgrades.toArray(ret);
				
				Arrays.sort(ret);
				int med = (ret.length / 2) - 1;
				if (med < 0)
					med = 0;
				
				res.setMedian(ret[med]);
				res.setDestribution(grades);
				res.setAverage(sum/count);
			}
			return res;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}

	/**
	 * Retrieves statistic report by student
	 * @param studentId Student Id
	 * @return Report
	 */
	public static StatisticReportItem getStudentStatisticReport(int studentId) {
		DBManager db = DBManager.getInstance();	
		String sql = "SELECT se.grade FROM aes.exam_execution ee, aes.student_exam se " +
					 "WHERE ee.code=se.examCode AND se.studentId=? AND ee.status=4 AND se.isApproved=1;";
		try {
			StatisticReportItem res = new StatisticReportItem();
			PreparedStatement stmt = db.getConnection().prepareStatement(sql);
			stmt.setInt(1, studentId);
			
			ResultSet rs = stmt.executeQuery();
			int sum = 0, count = 0;
			int[] grades = new int[] {0, 0, 0, 0, 0, 0, 0, 0, 0};
			ArrayList<Integer> lstgrades = new ArrayList<Integer>();
			while (rs.next()) {
				int grade = rs.getInt("grade");
				lstgrades.add(grade);
				if(grade >=0 && grade<55){
					grades[0]++;
				}
				else if(grade >=55 && grade<65){
					grades[1]++;
				}
				else if(grade >=65 && grade<70){
					grades[2]++;
				}
				else if(grade >=70 && grade<75){
					grades[3]++;
				}
				else if(grade >=75 && grade<80){
					grades[4]++;
				}
				else if(grade >=80 && grade<85){
					grades[5]++;
				}
				else if(grade >=85 && grade<90){
					grades[6]++;
				}
				else if(grade >=90 && grade<95){
					grades[7]++;
				}
				else if(grade >=95 && grade<=100){
					grades[8]++;
				}
				sum += grade;
				count++;
			}
			rs.close();
			if (count > 0) {
				for (int i=0; i<9; i++)
					grades[i] = (int)(grades[i]*100/count);
				Integer[] ret = new Integer[lstgrades.size()];
				lstgrades.toArray(ret);
				
				Arrays.sort(ret);
				int med = (ret.length / 2) - 1;
				if (med < 0)
					med = 0;
				
				res.setMedian(ret[med]);
				res.setDestribution(grades);
				res.setAverage(sum/count);
			}
			return res;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}
}
